"""
数据库连接和会话管理
"""
import logging
from sqlalchemy import create_engine, event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import QueuePool
from config import settings

# 配置日志
logger = logging.getLogger(__name__)

# 创建数据库引擎
engine = create_engine(
    settings.DATABASE_URL,
    echo=settings.DEBUG,  # 调试模式下显示SQL语句
    poolclass=QueuePool,
    pool_size=settings.MYSQL_POOL_SIZE,
    max_overflow=settings.MYSQL_MAX_OVERFLOW,
    pool_pre_ping=True,   # 连接池自动检测连接有效性
    pool_recycle=settings.MYSQL_POOL_RECYCLE,
    connect_args={
        "charset": "utf8mb4",
        "autocommit": False,
        "init_command": "SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'"
    }
)

# 添加连接事件监听器
@event.listens_for(engine, "connect")
def set_sql_mode(dbapi_connection, connection_record):
    """设置SQL模式"""
    cursor = dbapi_connection.cursor()
    cursor.execute("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'")
    cursor.close()

@event.listens_for(engine, "checkout")
def ping_connection(dbapi_connection, connection_record, connection_proxy):
    """连接检查时执行ping"""
    cursor = dbapi_connection.cursor()
    try:
        cursor.execute("SELECT 1")
    except Exception as e:
        logger.error(f"数据库连接检查失败: {e}")
        raise Exception("数据库连接失败")
    finally:
        cursor.close()

# 创建会话工厂
SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine,
    expire_on_commit=False
)

# 创建基类
Base = declarative_base()

def get_db():
    """
    获取数据库会话的依赖函数
    """
    db = SessionLocal()
    try:
        yield db
        db.commit()
    except Exception as e:
        db.rollback()
        logger.error(f"数据库操作失败: {e}")
        raise
    finally:
        db.close()

def get_db_for_refresh():
    """
    获取数据库会话的依赖函数 - 专门用于refresh端点
    不自动处理异常，让refresh端点自己管理事务
    """
    db = SessionLocal()
    try:
        yield db
    except Exception as e:
        logger.error(f"数据库操作失败: {e}")
        raise
    finally:
        db.close()

def test_connection():
    """测试数据库连接"""
    try:
        from sqlalchemy import text
        with engine.connect() as conn:
            conn.execute(text("SELECT 1"))
        logger.info("数据库连接测试成功")
        return True
    except Exception as e:
        logger.error(f"数据库连接测试失败: {e}")
        return False